﻿using DocumentFormat.OpenXml.Drawing;
using DocumentFormat.OpenXml.Math;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Configuration;
using MiniSoftware;
using MT.Enterprise.Utils.Extensions;
using NP.BPMReportPlatform.BPMPlatform;
using NP.BPMReportPlatform.Common;
using NP.BPMReportPlatform.Contract.Dtos;
using NP.BPMReportPlatform.DBHelper;
using NP.BPMReportPlatform.ElectronicSign;
using NP.BPMReportPlatform.ElectronicSign.Dto;
using NP.BPMReportPlatform.Entities.BPM_Business.Contract;
using NP.BPMReportPlatform.Entities.BPM_Business.Reports;
using NP.BPMReportPlatform.Entities.BPM_Platform;
using NP.BPMReportPlatform.RecurringJobs.Dto;
using NP.BPMReportPlatform.Reports.OrderMaterialDetail.Dtos;
using NP.BPMReportPlatform.Utils;
using NP.BPMReportPlatform.WordHelper;
using NP.Enterprise.Utils.Excel.Dto;
using NP.Enterprise.Utils.Http;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Linq.Dynamic.Core;
using System.Threading.Tasks;
using Volo.Abp.Application.Dtos;
using Volo.Abp.Domain.Entities;
using static NP.BPMReportPlatform.Contract.Dtos.ContractEnum;

namespace NP.BPMReportPlatform.Contract
{
    public class ContractService : BPMReportPlatformAppService, IContractService
    {
        private readonly SqlSugarClient _dbBussiness;
        private readonly SqlSugarClient _dbPlatform;
        private readonly ContractStatusService _contractStatusService;
        private readonly IESService _eSService;
        private readonly ICommonService _commonService;
        private readonly IConfiguration _configuration;

        public ContractService(ISqlSugarAppService sqlSugarAppService, IBPMUtilsAppService bPMUtilsApp
            , IBPMPlatformAppService iBPMPlatformApp, INPRestHttpHelper nPRestHttp, ContractStatusService contractStatusService, IESService eSService,
            ICommonService commonService, IConfiguration configuration)
        {
            _dbBussiness = sqlSugarAppService.GetBusinessInstance();
            _dbPlatform = sqlSugarAppService.GetPlatformInstance();
            _contractStatusService = contractStatusService;
            _eSService = eSService;
            _configuration = configuration;
            _commonService = commonService;
        }

        #region 合同订立

        /// <summary>
        /// 根据条件
        /// </summary>
        /// <param name="searchQuery"></param>
        /// <returns></returns>
        public async Task<PagedResultDto<ContractUserListDto>> GetContractUserFormationList(SearchQueryDto searchQuery)
        {
            RefAsync<int> totalCount = 0;
            var currentWorkNumber = CurrentUser != null && CurrentUser.UserName != null ? CurrentUser.UserName : string.Empty;
            var userCodes = _commonService.GetUserByRoleFilter(currentWorkNumber, RelationshipTypeEnum.ER);
            userCodes = userCodes != null && userCodes.Count > 0 ? userCodes : new List<string>() { "-1" };
            bool isAdmin = userCodes.Contains("9999999999");
            var canNotFormationWorkNumber = _dbPlatform.Queryable<UserContractEntity>()
                .Where(p => p.ContractStatus != (int)ContractEnum.ContractStatusEnum.已作废
                && p.ContractStatus != (int)ContractEnum.ContractStatusEnum.已终止 && p.ContractStatus != (int)ContractEnum.ContractStatusEnum.已解除)
                .Where(p => userCodes.Contains(p.WorkNumber))
                .Select(p => p.WorkNumber).Distinct().ToList();
            var result = await _dbPlatform.Queryable<Users, UserExtension>((p, a) => p.Source == a.Id)
                 .Where((p, a) => p.Status == 1 && a.Contract != "E" && a.Contract != "G") // 实习生,退休返聘的都过滤掉，TODO:退休返聘上线后，再过滤
                 .WhereIF(!isAdmin, (p, a) => SqlFunc.ContainsArray(userCodes, p.WorkNumber))
                 .Where((p, a) => !SqlFunc.ContainsArray(canNotFormationWorkNumber, p.WorkNumber))
                 .WhereIF(!string.IsNullOrEmpty(searchQuery.WorkNumber), (p, a) => p.WorkNumber.Contains(searchQuery.WorkNumber))
                 .WhereIF(!string.IsNullOrEmpty(searchQuery.Name), (p, a) => p.UserName.Contains(searchQuery.Name))
                 .WhereIF(!string.IsNullOrEmpty(searchQuery.Region), (p, a) => a.RegionName.Contains(searchQuery.Region))
                 .WhereIF(!string.IsNullOrEmpty(searchQuery.Department), (p, a) => p.FullPathText.Contains(searchQuery.Department))
                 .WhereIF(!string.IsNullOrEmpty(searchQuery.Position), (p, a) => a.Employeepostname.Contains(searchQuery.Position))
                 .WhereIF(searchQuery.JoinedStartDate != null, (p, a) => a.Contractbegin != null && DateTime.Parse(a.Contractbegin) >= searchQuery.JoinedStartDate)
                 .WhereIF(searchQuery.JoinedEndDate != null, (p, a) => a.Contractbegin != null && DateTime.Parse(a.Contractbegin) <= searchQuery.JoinedEndDate)
                 .Select((p, a) => new ContractUserListDto()
                 {
                     WorkNumber = p.WorkNumber,
                     UserId = p.UserId.ToString(),
                     Name = p.UserName,
                     RegionName = a.RegionName,
                     DepartmentName = p.FullPathText,
                     Position = a.Employeepostname,
                     JoinedDate = a.Contractbegin
                 }).ToPageListAsync(searchQuery.PageIndex, searchQuery.PageSize, totalCount);
            var allUserId = result?.Select(p => p.WorkNumber)?.ToList();
            if (allUserId != null && allUserId.Count > 0)
            {
                var allContractUsers = _dbPlatform.Queryable<UserContractEntity>()
                    .Where(p => allUserId.Contains(p.WorkNumber) && p.ContractStatus != (int)ContractEnum.ContractStatusEnum.已作废 &&
                    p.ContractStatus != (int)ContractEnum.ContractStatusEnum.已终止 && p.ContractStatus != (int)ContractEnum.ContractStatusEnum.已解除)
                    .Select(p => p).ToList();

                result?.ForEach(m =>
                {
                    m.DepartmentName = !string.IsNullOrEmpty(m.DepartmentName) ? m.DepartmentName.Replace("_", " | ") : string.Empty;

                    var list = allContractUsers?.Where(p => p.WorkNumber == m.WorkNumber).ToList();
                    if (list == null || list.Count == 0)
                    {
                        m.IsCanFormation = true;
                    }
                    else
                    {
                        m.IsCanFormation = false;
                    }
                });
            }

            return new PagedResultDto<ContractUserListDto>()
            {
                TotalCount = totalCount,
                Items = result
            };
        }

        /// <summary>
        /// 根据人员主键ID初始化订立合同基本信息
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="isDetail">是否是查看明细</param>
        /// <returns></returns>
        public UserContractEntity PackageUserContractInfo(string userId, bool isDetail = false)
        {
            if (isDetail == false)
            {
                DateTime dateTime = DateTime.Now;
                var result = _dbPlatform.Queryable<UserExtension, Users>((p, a) => p.Sapemployeeid == a.WorkNumber)
                      .Where((p, a) => a.UserId.ToString() == userId)
                      .Where((p, a) => a.Status == 1)
                      .Select((p, a) => new UserContractEntity()
                      {
                          UserName = a.UserName,
                          WorkNumber = a.WorkNumber,
                          // WorkAddress = p.Citycode,
                          // WorkAddressName = p.Cityname,
                          MobilePhone = p.Mobile,
                          UserPosnc = p.Employeepostname,
                          DepartName = a.FullPathText,
                          ContractCode = "",
                          ContractTypeCode = "LD",
                          ContractTypeName = "劳动合同",
                          ContractDeadLineCode = 1,
                          ContractDeadLineName = "短期合同",
                          HasSYQ = true,
                          CorporateName = "",
                          CorporatePostCode = "",
                          CorporateCode = "",
                          CorporatePhone = "",
                          CorporateAddress = "",
                          ContractCount = 1,
                          SFPushErrorMsg = p.Contractbegin,
                          SignFlowId = string.Empty,
                          FileKey = string.Empty,
                          SignErrorMsg = string.Empty,
                      }).First();
                if (result != null)
                {
                    var second = _dbPlatform.Queryable<UserExtensionSecondEntity>()
                        .Where(p => p.WorkNumber == result.WorkNumber)
                        .Select(p => p).First();
                    if (second != null)
                    {
                        result.UserRegionCode = second.RegionCode;
                        result.UserRegionName = second.RegionName;
                        result.CardAddress = second.CardAddress;
                        result.CardName = second.CardTypeName;
                        result.CardNum = second.CardNum;
                        result.ContactAddress = second.ContactAddress;
                        result.CategoryType = second.CategoryType;
                        result.PostCode = second.PostCode;
                        result.TimeSheetType = second.TimeSheetType;
                        result.School = second.School;
                        result.EndDate = second.EndDate;
                    }
                }
                if (result != null && !string.IsNullOrEmpty(result.DepartName))
                {
                    result.ContractStartDate = !string.IsNullOrEmpty(result.SFPushErrorMsg)
                        ? DateTime.Parse(DateTime.Parse(result.SFPushErrorMsg).ToString("yyyy-MM-dd")) : null;
                    result.SFPushErrorMsg = string.Empty;
                    result.ContractCount = 1;
                    result.SYQMonth = 6;
                    result.DepartName = result.DepartName.Replace("_", " | ");
                    result.SignDate = result.ContractStartDate != null ?
                        result.ContractStartDate : DateTime.Parse(dateTime.ToString("yyyy-MM-dd"));
                    result.SYQStartDate = result.ContractStartDate != null ?
                        result.ContractStartDate : DateTime.Parse(dateTime.ToString("yyyy-MM-dd"));
                    result.SYQEndDate = result.ContractStartDate != null ?
                        DateTime.Parse(result.ContractStartDate?.AddMonths(6).AddDays(-1).ToString("yyyy-MM-dd")) :
                        DateTime.Parse(dateTime.AddMonths(6).AddDays(-1).ToString("yyyy-MM-dd"));
                    var currentUser = CurrentUser != null && CurrentUser.Id != null ? CurrentUser.Id : Guid.NewGuid();
                    var res = _dbPlatform.Queryable<Users, UserExtension, CorporateEntity>((p, a, b) => p.Source == a.Id && a.Sapcompanycode == b.CorporateCode)
                        .Where((p, a, b) => p.UserId == currentUser && p.Status == 1)
                        .Select((p, a, b) => b).First();
                    if (res != null)
                    {
                        result.CorporateName = res.CorporateName;
                        result.CorporateCode = res.CorporateCode;
                        result.CorporatePhone = res.CorporatePhone;
                        result.CorporatePostCode = res.CorporatePostCode;
                        result.CorporateAddress = res.CorporateAddress;
                    }
                }
                return result;
            }
            else
            {
                return _dbPlatform.Queryable<UserContractEntity>().Where(p => p.WorkNumber == userId)
                    .Where(p => p.ContractStatus != (int)ContractEnum.ContractStatusEnum.已作废)
                    .OrderBy(p => p.ContractCount, OrderByType.Desc)
                    .OrderBy(p => p.CreateDate, OrderByType.Desc)
                    .Select(p => p).First();
            }
        }

        /// <summary>
        /// 根据数据获取当前合同编码
        /// </summary>
        /// <param name="contractType">合同类型</param>
        /// <param name="contractStartDate">合同开始时间</param>
        /// <param name="workNumber">员工编码</param>
        /// <param name="parentContractCode">原始合同：上一份合同</param>
        /// <returns></returns>
        public string GenerateContractCode(string contractType, string contractStartDate, string workNumber, string parentContractCode)
        {
            DateTime dt = DateTime.Now;
            DateTime.TryParse(contractStartDate, out dt);
            string contractC = $"NP-{contractType}-{dt.ToString("yyyy")}-{workNumber}";
            var index = _dbPlatform.Queryable<UserContractEntity>()
                .Where(p => p.ContractStatus != (int)ContractEnum.ContractStatusEnum.已作废)
                .Where(p => p.ContractCode.Contains(contractC) || p.ContractCode == parentContractCode)
                .OrderBy(p => p.ContractCount, OrderByType.Desc).Select(p => p.ContractCount ?? 0).First();
            return $"{contractC}-{index + 1}";
        }

        /// <summary>
        /// 保存合同订立
        /// </summary>
        /// <param name="entity">数据</param>
        /// <param name="type">1：立即生效 2：电子签</param>
        public async Task<string> SaveUserContract(UserContractEntity entity, int type)
        {
            string msg = "失败";
            if (entity != null)
            {
                var message = ValidateEnetity(entity);
                if (!string.IsNullOrEmpty(message))
                {
                    msg = message;
                }
                else
                {
                    // 是否是续签
                    var currentWorkNumber = CurrentUser != null && CurrentUser.UserName != null ? CurrentUser.UserName : string.Empty;
                    bool isRenewal = !string.IsNullOrEmpty(entity.ParentContractCode);
                    var user = _dbPlatform.Queryable<Users>().Where(p => p.WorkNumber == currentWorkNumber).Select(p => p).First();
                    entity.ContractId = Guid.NewGuid().ToString();
                    entity.CreateDate = DateTime.Now;
                    entity.CreateUserName = user?.UserName;
                    entity.CreateUserWorkNumber = user?.WorkNumber;
                    entity.ContractStatus = ContractStatusEnum.已作废.GetHashCode();
                    entity.ContractRenewalStatus = null;
                    entity.ContractRenewalStatusName = string.Empty;
                    entity.SFPushStatus = 0;
                    entity.SFPushDate = null;
                    entity.SFPushErrorMsg = string.Empty;
                    entity.ContractStatusName = "异常作废";
                    var flag = _dbPlatform.Insertable(entity).ExecuteCommand();

                    // 立即生效
                    if (type == 1)
                    {
                        _contractStatusService.UpdateContractStatus(entity.ContractId, ContractEnum.ContractStatusEnum.履行中);
                        msg = "成功";
                    }

                    // 电子签
                    if (type == 2)
                    {
                        ESBusinessEnum businessEnum = ESBusinessEnum.Contract;
                        // 合同模版名
                        WordTemplateEnum templateType = WordTemplateEnum.ContractTemplate;
                        Dictionary<string, object> values = null;

                        // 实习协议
                        if (entity.ContractCode.Contains("-SX-"))
                        {
                            businessEnum = ESBusinessEnum.InternContract;
                            templateType = WordTemplateEnum.InternContractTemplate;
                            values = PackageValuesByType(false, false, entity);
                        }
                        // 退休返聘
                        else if (entity.ContractCode.Contains("-FP-"))
                        {
                            businessEnum = ESBusinessEnum.RetirementContract;
                            templateType = entity.IsLB == 1 ? WordTemplateEnum.RetirementContractTemplate : WordTemplateEnum.RetirementOutContractTemplate;
                            values = PackageValuesByType(false, false, entity);
                        }
                        else
                        {
                            // 是否IU
                            var userareas = _dbPlatform.Queryable<Dictionaries>().Where(t => t.TypeCode == "HR_IU").Select(t => t.Value).ToList();
                            var userExtension = _dbPlatform.Queryable<UserExtension>()
                                   .Where(a => a.Sapemployeeid == entity.WorkNumber && (userareas.Contains(a.Objid_1) || userareas.Contains(a.Objid_2) || userareas.Contains(a.Objid_3)))
                                   .First();
                            // 是否IColor
                            var icoloruserareas = _dbPlatform.Queryable<Dictionaries>()
                                 .Where(t => t.TypeCode == "HR_IColor").Select(t => t.Value).ToList();

                            var iColorUserExtension = _dbPlatform.Queryable<UserExtension>()
                                  .Where(a => a.Sapemployeeid == entity.WorkNumber && (icoloruserareas.Contains(a.Objid_1) || icoloruserareas.Contains(a.Objid_2) || icoloruserareas.Contains(a.Objid_3)))
                                  .First();

                            var isIU = userExtension == null ? false : true;
                            var isIColor = iColorUserExtension == null ? false : true;

                            // 合同模版名
                            templateType = isRenewal ?
                                (isIU ? WordTemplateEnum.IUContractRenewalTemplate : isIColor ? WordTemplateEnum.IColorContractRenewalTemplate : WordTemplateEnum.ContractRenewalTemplate) :
                                (isIU ? WordTemplateEnum.IUContractTemplate : isIColor ? WordTemplateEnum.IColorContractTemplate : WordTemplateEnum.ContractTemplate);
                            values = PackageValuesByType(isRenewal, isIU, entity);
                        }

                        var memory = MiniWordHelper.CreatePdf(templateType, values);

                        // 上传PDF签署文件
                        entity.FileKey = await _eSService.UploadSignFile(entity.ContractCode, memory);
                        _dbPlatform.Updateable(entity).UpdateColumns(t => t.FileKey).Where(p => p.ContractId == entity.ContractId).ExecuteCommand();

                        // 合同签署流程发起
                        var taskId = await _eSService.CreateSignFlow(entity.ContractCode, businessEnum);
                        if (!string.IsNullOrEmpty(taskId))
                        {
                            _contractStatusService.UpdateContractStatus(entity.ContractId, ContractEnum.ContractStatusEnum.签署中);
                            msg = "成功";
                        }
                    }

                    if (flag > 0)
                    {
                        // 完善数据，已作废，但是末尾没有_S的数据，都统一更新一下
                        var errorContractData = _dbPlatform.Queryable<UserContractEntity>().Where(x => x.ContractCode == entity.ContractCode
                            && x.ContractStatus == (int)ContractStatusEnum.已作废 && x.ContractId != entity.ContractId &&
                            !SqlFunc.EndsWith(x.ContractCode, "_S")).ToList();
                        foreach (var errorContract in errorContractData)
                        {
                            errorContract.ContractCode = entity.ContractCode + "_S";
                            _dbPlatform.Updateable(errorContract).UpdateColumns(x => x.ContractCode).ExecuteCommand();
                        }
                    }
                }
            }
            else
            {
                msg = "对不起，数据不能为空";
            }

            return msg;
        }

        /// <summary>
        /// 电子签推送失败重推
        /// </summary>
        /// <param name="contractId">合同ID</param>
        /// <returns></returns>
        public async Task<string> ReRequestESignSyncData(string contractId)
        {
            string msg = "失败";
            var entity = _dbPlatform.Queryable<UserContractEntity>().First(x => x.ContractId == contractId &&
                x.ContractStatus == (int)ContractStatusEnum.签署中 && string.IsNullOrEmpty(x.SignFlowId));
            if (entity != null)
            {
                var message = ValidateEnetity(entity);
                if (!string.IsNullOrEmpty(message))
                {
                    msg = message;
                }
                else
                {
                    // 是否是续签
                    bool isRenewal = !string.IsNullOrEmpty(entity.ParentContractCode);

                    // 电子签
                    ESBusinessEnum businessEnum = ESBusinessEnum.Contract;
                    // 合同模版名
                    WordTemplateEnum templateType = WordTemplateEnum.ContractTemplate;
                    Dictionary<string, object> values = null;

                    // 实习协议
                    if (entity.ContractCode.Contains("-SX-"))
                    {
                        businessEnum = ESBusinessEnum.InternContract;
                        templateType = WordTemplateEnum.InternContractTemplate;
                        values = PackageValuesByType(false, false, entity);
                    }
                    // 退休返聘
                    else if (entity.ContractCode.Contains("-FP-"))
                    {
                        businessEnum = ESBusinessEnum.RetirementContract;
                        templateType = entity.IsLB == 1 ? WordTemplateEnum.RetirementContractTemplate : WordTemplateEnum.RetirementOutContractTemplate;
                        values = PackageValuesByType(false, false, entity);
                    }
                    else
                    {
                        // 是否IU
                        var userareas = _dbPlatform.Queryable<Dictionaries>().Where(t => t.TypeCode == "HR_IU").Select(t => t.Value).ToList();
                        var userExtension = _dbPlatform.Queryable<UserExtension>()
                               .Where(a => a.Sapemployeeid == entity.WorkNumber && (userareas.Contains(a.Objid_1) || userareas.Contains(a.Objid_2) || userareas.Contains(a.Objid_3)))
                               .First();
                        // 是否IColor
                        var icoloruserareas = _dbPlatform.Queryable<Dictionaries>()
                             .Where(t => t.TypeCode == "HR_IColor").Select(t => t.Value).ToList();

                        var iColorUserExtension = _dbPlatform.Queryable<UserExtension>()
                              .Where(a => a.Sapemployeeid == entity.WorkNumber && (icoloruserareas.Contains(a.Objid_1) || icoloruserareas.Contains(a.Objid_2) || icoloruserareas.Contains(a.Objid_3)))
                              .First();

                        var isIU = userExtension == null ? false : true;
                        var isIColor = iColorUserExtension == null ? false : true;

                        // 合同模版名
                        templateType = isRenewal ?
                            (isIU ? WordTemplateEnum.IUContractRenewalTemplate : isIColor ? WordTemplateEnum.IColorContractRenewalTemplate : WordTemplateEnum.ContractRenewalTemplate) :
                            (isIU ? WordTemplateEnum.IUContractTemplate : isIColor ? WordTemplateEnum.IColorContractTemplate : WordTemplateEnum.ContractTemplate);
                        values = PackageValuesByType(isRenewal, isIU, entity);
                    }

                    var memory = MiniWordHelper.CreatePdf(templateType, values);

                    // 上传PDF签署文件
                    entity.FileKey = await _eSService.UploadSignFile(entity.ContractCode, memory);
                    _dbPlatform.Updateable(entity).UpdateColumns(t => t.FileKey).Where(p => p.ContractId == entity.ContractId).ExecuteCommand();

                    // 合同签署流程发起
                    var taskId = await _eSService.CreateSignFlow(entity.ContractCode, businessEnum);
                    if (!string.IsNullOrEmpty(taskId))
                    {
                        _contractStatusService.UpdateContractStatus(entity.ContractId, ContractStatusEnum.签署中);
                        msg = "成功";
                    }
                }
            }
            else
            {
                msg = "对不起，数据不能为空";
            }
            return msg;
        }
        #endregion

        #region 合同续订
        /// <summary>
        /// 根据条件
        /// </summary>
        /// <param name="searchQuery"></param>
        /// <returns></returns>
        public async Task<PagedResultDto<ContractUserListDto>> GetContractUserRenewalList(SearchQueryDto searchQuery)
        {
            RefAsync<int> totalCount = 0;
            var currentWorkNumber = CurrentUser != null && CurrentUser.UserName != null ? CurrentUser.UserName : string.Empty;
            var userCodes = new List<string>();

            // 流程列表查询
            if (searchQuery.IsProcessListSearch == true)
            {
                userCodes = _commonService.GetUserByRoleFilter(currentWorkNumber, RelationshipTypeEnum.HRBP);
            }
            else
            {
                userCodes = _commonService.GetUserByRoleFilter(currentWorkNumber, RelationshipTypeEnum.ER);
            }
            userCodes = userCodes != null && userCodes.Count > 0 ? userCodes : new List<string>() { "-1" };
            bool isAdmin = userCodes.Contains("9999999999");
            var result = await _dbPlatform.Queryable<UserContractEntity>()
                .WhereIF(!isAdmin, p => userCodes.Contains(p.WorkNumber))
                .WhereIF(searchQuery.IsContractList == null || searchQuery.IsContractList == false, p => p.ContractStatus == (int)ContractEnum.ContractStatusEnum.履行中 || p.ContractStatus == (int)ContractEnum.ContractStatusEnum.已终止)
                .WhereIF(!string.IsNullOrEmpty(searchQuery.WorkNumber), p => p.WorkNumber.Contains(searchQuery.WorkNumber))
                .WhereIF(!string.IsNullOrEmpty(searchQuery.Name), p => p.UserName.Contains(searchQuery.Name))
                .WhereIF(!string.IsNullOrEmpty(searchQuery.Region), p => p.UserRegionName.Contains(searchQuery.Region))
                .WhereIF(!string.IsNullOrEmpty(searchQuery.Department), p => p.DepartName.Contains(searchQuery.Department))
                .WhereIF(!string.IsNullOrEmpty(searchQuery.ContractTypeCode), p => p.ContractTypeCode == searchQuery.ContractTypeCode)
                .WhereIF(!string.IsNullOrEmpty(searchQuery.ContractDeadLineCode), p => p.ContractDeadLineCode.ToString() == searchQuery.ContractDeadLineCode)
                .WhereIF(!string.IsNullOrEmpty(searchQuery.ContractCode), p => p.ContractCode.Contains(searchQuery.ContractCode))
                .WhereIF(searchQuery.ContractStartDate != null, p => p.ContractStartDate >= searchQuery.ContractStartDate)
                .WhereIF(searchQuery.ContractStartDate1 != null, p => p.ContractStartDate <= searchQuery.ContractStartDate1)
                .WhereIF(searchQuery.ContractEndDate != null, p => p.ContractEndDate >= searchQuery.ContractEndDate)
                .WhereIF(searchQuery.ContractEndDate1 != null, p => p.ContractEndDate <= searchQuery.ContractEndDate1)
                .WhereIF(searchQuery.ContractRenewalStatusCode != null && searchQuery.ContractRenewalStatusCode != -1, p => p.ContractRenewalStatus == searchQuery.ContractRenewalStatusCode)
                .WhereIF(searchQuery.ContractStatusCode != null && searchQuery.ContractStatusCode != -1, p => p.ContractStatus == searchQuery.ContractStatusCode)
                .WhereIF(searchQuery.IsContractList != true, p => !p.ContractCode.Contains("SX") && !p.ContractCode.Contains("FP")) // 过滤掉实习和返聘的合同
                .OrderBy(p => p.CreateDate, OrderByType.Desc)
                .Select(p => new ContractUserListDto()
                {
                    WorkNumber = p.WorkNumber,
                    Name = p.UserName,
                    RegionName = p.UserRegionName,
                    DepartmentName = p.DepartName,
                    Position = p.UserPosnc,
                    ContractStartDate = p.ContractStartDate,
                    ContractEndDate = p.ContractEndDate,
                    ContractCode = p.ContractCode,
                    ContractCount = p.ContractCount ?? 1,
                    ContractTypeName = p.ContractTypeName,
                    ContractRenewalStatusName = p.ContractRenewalStatusName,
                    ContractRenewalStatus = p.ContractRenewalStatus,
                    ContractStatus = p.ContractStatus,
                    ContractStatusName = p.ContractStatusName,
                    ProcessId = p.SignFlowId,
                    InstanceNumber = p.InstanceNumber,
                    ContractDeadLineCode = p.ContractDeadLineCode,
                    ContractDeadLineName = p.ContractDeadLineName,
                    SignFlowId = p.SignFlowId,
                    IsCanGenerateContract = !string.IsNullOrEmpty(p.SignFlowId) &&
                    (p.ContractStatus == (int)ContractEnum.ContractStatusEnum.履行中
                    || p.ContractStatus == (int)ContractEnum.ContractStatusEnum.未生效
                    || p.ContractStatus == (int)ContractEnum.ContractStatusEnum.已解除
                    || p.ContractStatus == (int)ContractEnum.ContractStatusEnum.已终止),
                    IsCanCuiBan = !string.IsNullOrEmpty(p.SignFlowId) && p.ContractStatus == (int)ContractEnum.ContractStatusEnum.签署中,
                    IsCanCancel = (p.ContractStatus == (int)ContractEnum.ContractStatusEnum.签署中 || p.ContractStatus == (int)ContractEnum.ContractStatusEnum.履行中
                    || p.ContractStatus == (int)ContractEnum.ContractStatusEnum.未生效),
                    IsCanInitiateProcess = string.IsNullOrEmpty(p.InstanceNumber),
                    SFPushMessage = p.SFPushErrorMsg,
                    SFPushStatus = p.SFPushStatus ?? 0
                }).ToPageListAsync(searchQuery.PageIndex, searchQuery.PageSize, totalCount);
            var allUserId = result?.Select(p => p.WorkNumber)?.ToList();
            if (allUserId != null && allUserId.Count() > 0)
            {
                // 续签合同可发起条件：合同状态为：履行中 或者 已终止  合同续签状态为：空 或者 已作废
                var allContractCodes = result?.Select(p => p.ContractCode)?.ToList();
                var renerateContracts = _dbPlatform.Queryable<UserContractEntity>().Where(p =>
                allContractCodes.Contains(p.ParentContractCode)).OrderBy(t => t.ContractStatus).Select(p => p).ToList();

                var allInstanceNumber = result?.Select(p => p.InstanceNumber)?.ToList();
                var totalInstances = _dbBussiness.Queryable<UserContractRenewalProcessEntity>()
                    .Where(t => allInstanceNumber.Contains(t.InstanceNumber)).Select(t => new { t.StatusName, t.InstanceNumber }).ToList();

                result?.ForEach(m =>
                {
                    m.SFPushMessage = m.SFPushStatus == 1 ? "推送成功" : m.SFPushStatus == -1 ? ("推送失败:" + (m.SFPushMessage ?? "")) : "未推送";
                    m.InstanceStatusName = totalInstances.Where(t => t.InstanceNumber == m.InstanceNumber).FirstOrDefault()?.StatusName;

                    m.IsSign = !string.IsNullOrEmpty(m.SignFlowId) ? "是" : "否";
                    var match = renerateContracts?.Find(t => t.ParentContractCode == m.ContractCode);
                    if (match == null || match.ContractStatus == (int)ContractStatusEnum.已作废)
                    {
                        if ((m.ContractRenewalStatus == null ||
                             m.ContractStatus == (int)ContractEnum.ContractStatusEnum.履行中 ||
                             m.ContractStatus == (int)ContractEnum.ContractStatusEnum.已终止) && m.ContractDeadLineCode != 3)
                        {
                            m.IsCanRenewal = true;
                        }
                        else
                        {
                            m.IsCanRenewal = false;
                        }
                    }
                    else
                    {
                        m.IsCanRenewal = false;
                    }
                });
            }
            //返回结果
            result?.ForEach(m =>
            {
                m.DepartmentName = m.DepartmentName.Replace("_", " | ");
            });
            return new PagedResultDto<ContractUserListDto>()
            {
                TotalCount = totalCount,
                Items = result
            };
        }

        /// <summary>
        /// 根据人员工号获取续订合同信息
        /// </summary>
        /// <param name="workNumber"></param>
        /// <param name="parentContractCode">上一份合同</param>
        /// <returns></returns>
        public ContractRenewalDto SearchUserContract(string workNumber, string parentContractCode)
        {
            ContractRenewalDto result = new ContractRenewalDto();
            var res = _dbPlatform.Queryable<UserContractEntity>()
                  .Where(p => p.WorkNumber == workNumber && p.ContractCode == parentContractCode)
                  .Where(p => p.ContractStatus != (int)ContractEnum.ContractStatusEnum.已作废)
                  .OrderBy(p => p.CreateDate, OrderByType.Desc)
                  .Select(p => p).First();

            result.RenewalContract = res;

            if (res != null)
            {
                // 续签合同，员工基本信息取员工身上的，不要取上一份合同的
                var second = _dbPlatform.Queryable<UserExtensionSecondEntity>()
                       .Where(p => p.WorkNumber == workNumber)
                       .Select(p => p).First();

                // 续签合同，员工基本信息取员工身上的，不要取上一份合同的
                var userextend = _dbPlatform.Queryable<UserExtension>()
                       .Where(p => p.Sapemployeeid == workNumber)
                       .Select(p => p).First();

                if (second != null)
                {
                    result.RenewalContract.UserRegionCode = second.RegionCode;
                    result.RenewalContract.UserRegionName = second.RegionName;
                    result.RenewalContract.CardAddress = second.CardAddress;
                    result.RenewalContract.CardName = second.CardTypeName;
                    result.RenewalContract.CardNum = second.CardNum;
                    result.RenewalContract.ContactAddress = second.ContactAddress;
                    result.RenewalContract.CategoryType = second.CategoryType;
                    result.RenewalContract.PostCode = second.PostCode;
                    result.RenewalContract.TimeSheetType = second.TimeSheetType;
                    result.RenewalContract.MobilePhone = userextend?.Mobile;
                }

                result.OriginalContractCode = res.ContractCode;
                result.OriginalContractStartDate = res.ContractStartDate;
                result.OriginalContractEndDate = res.ContractEndDate;
                result.OriginalContractDeadLineCode = res.ContractDeadLineCode;
                result.OriginalContractDeadLineName = res.ContractDeadLineName;
                result.OriginalContractTypeName = res.ContractTypeName;
                result.OriginalCorporateName = res.CorporateName;
                result.OriginalSignDate = res.SignDate;
                result.OriginalWorkAddress = res.WorkAddress;
                result.OriginalWorkAddressName = res.WorkAddressName;
                result.RenewalContract.ContractCode = GenerateContractCode(result.RenewalContract.ContractTypeCode, result.RenewalContract.ContractStartDate?.ToString("yyyy-MM-dd"), result.RenewalContract.WorkNumber, parentContractCode);
                var count = !string.IsNullOrEmpty(result.RenewalContract.ContractCode) ? result.RenewalContract.ContractCode.Split("-").LastOrDefault() : "2";
                int index = 2;
                int.TryParse(count, out index);
                result.RenewalContract.ContractDeadLineCode = index == 2 ? ContractEnum.ContractTypeEnum.中长期合同.GetHashCode() :
                    ContractEnum.ContractTypeEnum.无固定期限.GetHashCode();
                result.RenewalContract.ContractDeadLineName = index == 2 ? "中长期合同" : "无固定期限";
                result.RenewalContract.ParentContractCode = parentContractCode;
                result.RenewalContract.ContractCount = index;
                result.RenewalContract.SignFlowId = string.Empty;
                result.RenewalContract.SignErrorMsg = string.Empty;
                result.RenewalContract.FileKey = string.Empty;
                result.RenewalContract.ContractStatus = null;
                result.RenewalContract.ContractStatusName = string.Empty;

                // 匹配的合同流程信息
                var matchProcessContract = _dbBussiness.Queryable<UserContractRenewalProcessEntity>()
                     .Where(p => p.ContractCode == parentContractCode).OrderBy(p => p.ApproveDate, OrderByType.Desc)
                     .Select(p => p).First();
                if (matchProcessContract != null)
                {
                    result.RenewalContract.ContractDeadLineCode = !string.IsNullOrEmpty(matchProcessContract.ContractRenewalDeadLine)
                        ? int.Parse(matchProcessContract.ContractRenewalDeadLine) : null;
                    result.RenewalContract.ContractDeadLineName = matchProcessContract.ContractRenewalDeadLineName;
                    result.RenewalContract.ContractTypeCode = matchProcessContract.ContractRenewalTypeCode;
                    result.RenewalContract.ContractTypeName = matchProcessContract.ContractRenewalTypeName;
                    result.RenewalContract.SignDate = matchProcessContract.RenewalSignDate;
                    result.RenewalContract.WorkAddress = matchProcessContract.RenewalWorkAddress;
                    result.RenewalContract.WorkAddressName = matchProcessContract.RenewalWorkAddressName;
                    result.RenewalContract.ContractStartDate = matchProcessContract.ContractReniewStartDate;
                    var corporate = _dbPlatform.Queryable<CorporateEntity>().Where(p => p.CorporateCode == matchProcessContract.RenewalCorporateCode).Select(p => p).First();
                    if (corporate != null)
                    {
                        result.RenewalContract.CorporateCode = matchProcessContract.RenewalCorporateCode;
                        result.RenewalContract.CorporateName = matchProcessContract.RenewalCorporateName;
                        result.RenewalContract.CorporateAddress = corporate.CorporateAddress;
                        result.RenewalContract.CorporatePhone = corporate.CorporatePhone;
                        result.RenewalContract.CorporatePostCode = corporate.CorporatePostCode;
                    }
                }
                else
                {
                    if (result.OriginalContractDeadLineCode != ContractEnum.ContractTypeEnum.无固定期限.GetHashCode())
                    {
                        result.RenewalContract.ContractStartDate = result.OriginalContractEndDate?.AddDays(1);
                    }
                    result.RenewalContract.SignDate = result.RenewalContract.ContractStartDate;
                }
            }
            return result;
        }
        #endregion

        #region 合同列表
        /// <summary>
        /// 下载合同
        /// </summary>
        /// <param name="processId"></param>
        public async Task<string> DownloadContract(string processId)
        {
            var fileUrl = await _eSService.GetESDownloadUrl(processId);
            return fileUrl;
        }

        /// <summary>
        /// 流程任务 type:1 催办 2 作废 3 终止
        /// </summary>
        /// <param name="type"></param>
        public async Task<string> ProcessTask(int type, string contractCode)
        {
            string msg = string.Empty;
            if (type == 1)
            {
                await _eSService.SignFlowReminder(contractCode);
            }

            if (type == 2)
            {
                var flowId = _dbPlatform.Queryable<UserContractEntity>().Where(p => p.ContractCode == contractCode).Select(p => p.SignFlowId).First();
                if (!string.IsNullOrEmpty(flowId))
                {
                    msg = await _eSService.SignFlowCancel(contractCode);
                }
                else
                {
                    _contractStatusService.UpdateContractStatus(contractCode, ContractEnum.ContractStatusEnum.已作废);
                }
            }

            if (type == 3)
            {
                var contract = _dbPlatform.Queryable<UserContractEntity>().First(x => x.ContractCode == contractCode);
                if (contract != null && contract.ContractStatus == (int)ContractStatusEnum.履行中)
                {
                    contract.ContractStatus = (int)ContractStatusEnum.已终止;
                    contract.ContractStatusName = ContractStatusEnum.已终止.ToString();
                    _dbPlatform.Updateable(contract).ExecuteCommand();
                }
            }

            return msg;
        }

        /// <summary>
        /// 三方协议流程任务 type:1 催办 2 作废
        /// </summary>
        /// <param name="type"></param>
        public async Task<string> ProcessChangeTask(int type, string businessCode)
        {
            string msg = string.Empty;
            if (type == 1)
            {
                await _eSService.SignFlowReminder(businessCode);
            }

            if (type == 2)
            {
                var flowId = _dbPlatform.Queryable<UserContractTripartiteAgreementEntity>().Where(p => p.ChangeContractCode == businessCode).Select(p => p.SignFlowId).First();
                if (!string.IsNullOrEmpty(flowId))
                {
                    msg = await _eSService.SignFlowCancel(businessCode);
                }
                else
                {
                    _contractStatusService.UpdateChangeContractStatus(businessCode, ContractEnum.ContractChangeStatusEnum.已作废);
                }
            }

            return msg;
        }

        /// <summary>
        /// 根据合同编码获取合同基本信息
        /// </summary>
        /// <param name="contractCode">合同编码</param>
        /// <returns></returns>
        public UserContractEntity GetUserContractInfo(string contractCode)
        {
            return _dbPlatform.Queryable<UserContractEntity>()
                .Where(p => p.ContractCode == contractCode)
                .OrderBy(p => p.ContractCount, OrderByType.Asc)
                .OrderBy(p => p.CreateDate, OrderByType.Desc)
                .Select(p => p).First();
        }
        #endregion

        #region 实习生协议

        /// <summary>
        /// 根据条件获取实习生协议清单
        /// </summary>
        /// <param name="searchQuery"></param>
        /// <returns></returns>
        public async Task<PagedResultDto<ContractUserListDto>> GetIntelContractUserFormationList(SearchQueryDto searchQuery)
        {
            RefAsync<int> totalCount = 0;
            var currentWorkNumber = CurrentUser != null && CurrentUser.UserName != null ? CurrentUser.UserName : string.Empty;
            var userCodes = _commonService.GetUserByRoleFilter(currentWorkNumber, RelationshipTypeEnum.ER);
            userCodes = userCodes != null && userCodes.Count > 0 ? userCodes : new List<string>() { "-1" };
            bool isAdmin = userCodes.Contains("9999999999");
            var canNotFormationWorkNumber = _dbPlatform.Queryable<UserContractEntity>()
                .Where(p => p.ContractStatus != (int)ContractEnum.ContractStatusEnum.已作废
                && p.ContractStatus != (int)ContractEnum.ContractStatusEnum.已终止 && p.ContractStatus != (int)ContractEnum.ContractStatusEnum.已解除)
                .Where(p => userCodes.Contains(p.WorkNumber))
                .Select(p => p.WorkNumber).Distinct().ToList();
            var result = await _dbPlatform.Queryable<Users, UserExtension>((p, a) => p.Source == a.Id)
                 .Where((p, a) => p.Status == 1 && a.Contract == "E") // 只拿实习生
                 .WhereIF(!isAdmin, (p, a) => SqlFunc.ContainsArray(userCodes, p.WorkNumber))
                 .WhereIF(!string.IsNullOrEmpty(searchQuery.WorkNumber), (p, a) => p.WorkNumber.Contains(searchQuery.WorkNumber))
                 .WhereIF(!string.IsNullOrEmpty(searchQuery.Name), (p, a) => p.UserName.Contains(searchQuery.Name))
                 .WhereIF(!string.IsNullOrEmpty(searchQuery.Region), (p, a) => a.RegionName.Contains(searchQuery.Region))
                 .WhereIF(!string.IsNullOrEmpty(searchQuery.Department), (p, a) => p.FullPathText.Contains(searchQuery.Department))
                 .WhereIF(!string.IsNullOrEmpty(searchQuery.Position), (p, a) => a.Employeepostname.Contains(searchQuery.Position))
                 .WhereIF(searchQuery.JoinedStartDate != null, (p, a) => a.Contractbegin != null && DateTime.Parse(a.Contractbegin) >= searchQuery.JoinedStartDate)
                 .WhereIF(searchQuery.JoinedEndDate != null, (p, a) => a.Contractbegin != null && DateTime.Parse(a.Contractbegin) <= searchQuery.JoinedEndDate)
                 .Select((p, a) => new ContractUserListDto()
                 {
                     WorkNumber = p.WorkNumber,
                     UserId = p.UserId.ToString(),
                     Name = p.UserName,
                     RegionName = a.RegionName,
                     DepartmentName = p.FullPathText,
                     Position = a.Employeepostname,
                     JoinedDate = a.Contractbegin
                 }).ToPageListAsync(searchQuery.PageIndex, searchQuery.PageSize, totalCount);
            var allUserId = result?.Select(p => p.WorkNumber)?.ToList();
            if (allUserId != null && allUserId.Count > 0)
            {
                var allContractUsers = _dbPlatform.Queryable<UserContractEntity>()
                    .Where(p => allUserId.Contains(p.WorkNumber) && p.ContractStatus != (int)ContractEnum.ContractStatusEnum.已作废 &&
                    p.ContractStatus != (int)ContractEnum.ContractStatusEnum.已终止 && p.ContractStatus != (int)ContractEnum.ContractStatusEnum.已解除)
                    .Select(p => p).ToList();

                result?.ForEach(m =>
                {
                    m.DepartmentName = !string.IsNullOrEmpty(m.DepartmentName) ? m.DepartmentName.Replace("_", " | ") : string.Empty;
                    var list = allContractUsers?.Where(p => p.WorkNumber == m.WorkNumber).ToList();
                    m.ContractCode = list.FirstOrDefault()?.ContractCode;
                    m.ContractStatus = list.FirstOrDefault()?.ContractStatus;
                    m.ContractStatusName = list.FirstOrDefault()?.ContractStatusName;
                    if (list == null || list.Count == 0)
                    {
                        m.IsCanFormation = true;
                    }
                    else
                    {
                        m.IsCanFormation = false;
                    }
                });
            }

            return new PagedResultDto<ContractUserListDto>()
            {
                TotalCount = totalCount,
                Items = result
            };
        }

        #endregion

        #region 退休返聘

        /// <summary>
        /// 根据条件获取退休返聘
        /// </summary>
        /// <param name="searchQuery"></param>
        /// <returns></returns>
        public async Task<PagedResultDto<ContractUserListDto>> GetRetirementContractUserFormationList(SearchQueryDto searchQuery)
        {
            RefAsync<int> totalCount = 0;
            var currentWorkNumber = CurrentUser != null && CurrentUser.UserName != null ? CurrentUser.UserName : string.Empty;
            var userCodes = _commonService.GetUserByRoleFilter(currentWorkNumber, RelationshipTypeEnum.ER);
            userCodes = userCodes != null && userCodes.Count > 0 ? userCodes : new List<string>() { "-1" };
            bool isAdmin = userCodes.Contains("9999999999");
            var canNotFormationWorkNumber = _dbPlatform.Queryable<UserContractEntity>()
                .Where(p => p.ContractStatus != (int)ContractEnum.ContractStatusEnum.已作废
                && p.ContractStatus != (int)ContractEnum.ContractStatusEnum.已终止 && p.ContractStatus != (int)ContractEnum.ContractStatusEnum.已解除)
                .Where(p => userCodes.Contains(p.WorkNumber))
                .Select(p => p.WorkNumber).Distinct().ToList();
            var result = await _dbPlatform.Queryable<Users, UserExtension>((p, a) => p.Source == a.Id)
                 .Where((p, a) => p.Status == 1 && a.Contract == "G") // 退休返聘人员
                 .WhereIF(!isAdmin, (p, a) => SqlFunc.ContainsArray(userCodes, p.WorkNumber))
                 .WhereIF(!string.IsNullOrEmpty(searchQuery.WorkNumber), (p, a) => p.WorkNumber.Contains(searchQuery.WorkNumber))
                 .WhereIF(!string.IsNullOrEmpty(searchQuery.Name), (p, a) => p.UserName.Contains(searchQuery.Name))
                 .WhereIF(!string.IsNullOrEmpty(searchQuery.Region), (p, a) => a.RegionName.Contains(searchQuery.Region))
                 .WhereIF(!string.IsNullOrEmpty(searchQuery.Department), (p, a) => p.FullPathText.Contains(searchQuery.Department))
                 .WhereIF(!string.IsNullOrEmpty(searchQuery.Position), (p, a) => a.Employeepostname.Contains(searchQuery.Position))
                 .WhereIF(searchQuery.JoinedStartDate != null, (p, a) => a.Contractbegin != null && DateTime.Parse(a.Contractbegin) >= searchQuery.JoinedStartDate)
                 .WhereIF(searchQuery.JoinedEndDate != null, (p, a) => a.Contractbegin != null && DateTime.Parse(a.Contractbegin) <= searchQuery.JoinedEndDate)
                 .Select((p, a) => new ContractUserListDto()
                 {
                     WorkNumber = p.WorkNumber,
                     UserId = p.UserId.ToString(),
                     Name = p.UserName,
                     RegionName = a.RegionName,
                     DepartmentName = p.FullPathText,
                     Position = a.Employeepostname,
                     JoinedDate = a.Contractbegin
                 }).ToPageListAsync(searchQuery.PageIndex, searchQuery.PageSize, totalCount);
            var allUserId = result?.Select(p => p.WorkNumber)?.ToList();
            if (allUserId != null && allUserId.Count > 0)
            {
                var allContractUsers = _dbPlatform.Queryable<UserContractEntity>()
                    .Where(p => allUserId.Contains(p.WorkNumber) && p.ContractStatus != (int)ContractEnum.ContractStatusEnum.已作废 &&
                    p.ContractStatus != (int)ContractEnum.ContractStatusEnum.已终止 && p.ContractStatus != (int)ContractEnum.ContractStatusEnum.已解除)
                    .OrderBy(t => t.CreateDate, OrderByType.Desc).ToList();

                result?.ForEach(m =>
                {
                    m.DepartmentName = !string.IsNullOrEmpty(m.DepartmentName) ? m.DepartmentName.Replace("_", " | ") : string.Empty;

                    var list = allContractUsers?.Where(p => p.WorkNumber == m.WorkNumber).ToList();
                    if (list == null || list.Count == 0)
                    {
                        m.IsCanFormation = true;
                    }
                    else
                    {
                        m.ContractCode = list.FirstOrDefault()?.ContractCode;
                        m.ContractStatus = list.FirstOrDefault()?.ContractStatus;
                        m.ContractStatusName = list.FirstOrDefault()?.ContractStatusName;
                        // 3个月以内的，证明已经立过合同了，不允许再立了
                        if (list.FirstOrDefault()?.CreateDate < DateTime.Now.AddMonths(-3))
                        {
                            m.IsCanFormation = true;
                        }
                        else
                        {
                            m.IsCanFormation = false;
                        }
                    }
                });
            }

            return new PagedResultDto<ContractUserListDto>()
            {
                TotalCount = totalCount,
                Items = result
            };
        }

        #endregion

        #region 合同三方协议变更

        /// <summary>
        /// 合同三方协议变更
        /// </summary>
        /// <param name="searchQuery"></param>
        /// <returns></returns>
        public async Task<PagedResultDto<ContractUserListDto>> GetAllTripartiteContract(SearchQueryDto searchQuery)
        {
            RefAsync<int> totalCount = 0;
            var currentWorkNumber = CurrentUser != null && CurrentUser.UserName != null ? CurrentUser.UserName : string.Empty;
            var userCodes = _commonService.GetUserByRoleFilter(currentWorkNumber, RelationshipTypeEnum.ER);
            userCodes = userCodes != null && userCodes.Count > 0 ? userCodes : new List<string>() { "-1" };
            bool isAdmin = userCodes.Contains("9999999999");
            List<string> contractCodes = new List<string>();
            if (searchQuery.ChangeStatus != null && searchQuery.ChangeStatus != -1)
            {
                var contractCodesRes = _dbPlatform.Queryable<UserContractTripartiteAgreementEntity>()
                    .Where(p => p.ChangeStatus == searchQuery.ChangeStatus)
                    .Where(p => SqlFunc.ContainsArray(userCodes, p.PartyBCode))
                    .Select(p => p.OriginalContractCode).ToList();
                contractCodes = contractCodesRes != null && contractCodesRes.Count > 0 ? contractCodesRes : new List<string>() { "-1" };
            }
            var result = await _dbPlatform.Queryable<UserContractEntity>()
                .WhereIF(!isAdmin, p => userCodes.Contains(p.WorkNumber))
                .Where(p => p.ContractStatus == (int)ContractEnum.ContractStatusEnum.履行中 ||
                p.ContractStatus == (int)ContractEnum.ContractStatusEnum.已终止 || p.ContractStatus == (int)ContractEnum.ContractStatusEnum.已作废)
                .WhereIF(contractCodes != null && contractCodes.Count > 0, p => contractCodes.Contains(p.ContractCode))
                .WhereIF(!string.IsNullOrEmpty(searchQuery.WorkNumber), p => p.WorkNumber.Contains(searchQuery.WorkNumber))
                .WhereIF(!string.IsNullOrEmpty(searchQuery.Name), p => p.UserName.Contains(searchQuery.Name))
                .WhereIF(!string.IsNullOrEmpty(searchQuery.Region), p => p.UserRegionName.Contains(searchQuery.Region))
                .WhereIF(!string.IsNullOrEmpty(searchQuery.Department), p => p.DepartName.Contains(searchQuery.Department))
                .WhereIF(!string.IsNullOrEmpty(searchQuery.ContractCode), p => p.ContractCode.Contains(searchQuery.ContractCode))
                .WhereIF(searchQuery.ContractStartDate != null, p => p.ContractStartDate >= searchQuery.ContractStartDate)
                .WhereIF(searchQuery.ContractStartDate1 != null, p => p.ContractStartDate <= searchQuery.ContractStartDate1)
                .WhereIF(searchQuery.ContractEndDate != null, p => p.ContractEndDate >= searchQuery.ContractEndDate)
                .WhereIF(searchQuery.ContractEndDate1 != null, p => p.ContractEndDate <= searchQuery.ContractEndDate1)
                .Select(p => new ContractUserListDto()
                {
                    WorkNumber = p.WorkNumber,
                    Name = p.UserName,
                    ContractCode = p.ContractCode,
                    CardNum = p.CardNum,
                    WorkAddressName = p.WorkAddressName,
                    RegionName = p.UserRegionName,
                    DepartmentName = p.DepartName,
                    ContractStartDate = p.ContractStartDate,
                    ContractEndDate = p.ContractEndDate,
                    ContractTypeName = p.ContractTypeName,
                    ContractDeadLineName = p.ContractDeadLineName,
                }).ToPageListAsync(searchQuery.PageIndex, searchQuery.PageSize, totalCount);

            var codes = result != null && result.Count() > 0 ? result.Select(p => p.ContractCode).ToList() : null;
            var contracts = codes != null && codes.Count() > 0 ?
                _dbPlatform.Queryable<UserContractTripartiteAgreementEntity>()
                .Where(p => codes.Contains(p.OriginalContractCode))
                .WhereIF(searchQuery.ChangeStatus != null && searchQuery.ChangeStatus != -1, p => p.ChangeStatus == searchQuery.ChangeStatus)
                .Select(p => p).ToList() : null;
            result?.ForEach(p =>
            {
                var match = contracts?.Find(x => x.OriginalContractCode == p.ContractCode);
                p.ChangeStatusName = match != null ? match.ChangeStatusName : string.Empty;
                p.IsCanChange = match == null || match.ChangeStatus != (int)ContractEnum.ContractChangeStatusEnum.签署中;
            });
            return new PagedResultDto<ContractUserListDto>()
            {
                TotalCount = totalCount,
                Items = result
            };
        }

        /// <summary>
        /// 生成变更合同信息
        /// </summary>
        /// <param name="workNumber">员工编码</param>
        /// <param name="contractCode">合同编码</param>
        /// <returns></returns>
        public UserContractTripartiteAgreementEntity GenerateChangeContract(string workNumber, string contractCode)
        {
            UserContractTripartiteAgreementEntity res = new UserContractTripartiteAgreementEntity();
            int ind = 0;
            var index = _dbPlatform.Queryable<UserContractTripartiteAgreementEntity>()
                .OrderBy(p => p.CreateDate, OrderByType.Desc).Select(p => p.ChangeContractCode).First();
            var contractInfo = _dbPlatform.Queryable<UserContractTripartiteAgreementEntity>()
                .Where(p => p.OriginalContractCode == contractCode && p.ChangeStatus != ContractChangeStatusEnum.已作废.GetHashCode())
                .OrderBy(p => p.CreateDate, OrderByType.Desc).Select(p => p).First();
            if (contractInfo == null)
            {
                contractInfo = _dbPlatform.Queryable<UserContractEntity, CorporateEntity>((p, a) => new object[]
            {
                JoinType.Left, p.CorporateCode == a.CorporateCode
            })
                .Where((p, a) => p.ContractCode == contractCode)
                .OrderBy((p, a) => p.CreateDate, OrderByType.Desc)
                .Select((p, a) => new UserContractTripartiteAgreementEntity()
                {
                    OriginalContractCode = p.ContractCode,
                    OriginalContractEndDate = p.ContractEndDate,
                    OriginalContractStartDate = p.ContractStartDate,
                    PartyBAddress = p.ContactAddress,
                    PartyBCode = p.WorkNumber,
                    PartyBName = p.UserName,
                    PartyBCardNo = p.CardNum,
                    PartyAAddress = p.CorporateAddress,
                    PartyACode = p.CorporateCode,
                    PartyALegalPerson = a.LegalPerson,
                    PartyAName = p.CorporateName,
                    PartyCCode = p.CorporateCode,
                    PartyCName = p.CorporateName,
                    PartyCAddress = p.CorporateAddress,
                    PartyCLegalPerson = a.LegalPerson
                }).First();
            }
            if (!string.IsNullOrEmpty(index))
            {
                int.TryParse(index.Split("BG")?.LastOrDefault(), out ind);
                ind = ind + 1;
            }
            else
            {
                ind = 1;
            }
            if (contractInfo != null)
            {
                var user = _dbPlatform.Queryable<UserExtensionSecondEntity>().Where(t => t.WorkNumber == workNumber).First();
                if (user != null)
                {
                    res.PartyBAddress = user.ContactAddress;
                    res.PartyBCode = user.WorkNumber;
                    res.PartyBName = user.UserName;
                    res.PartyBCardNo = user.CardNum;
                }
                else
                {
                    res.PartyBAddress = contractInfo.PartyBAddress;
                    res.PartyBCode = contractInfo.PartyBCode;
                    res.PartyBName = contractInfo.PartyBName;
                    res.PartyBCardNo = contractInfo.PartyBCardNo;
                }

                res.OriginalContractCode = contractInfo.OriginalContractCode;
                res.OriginalContractEndDate = contractInfo.OriginalContractEndDate;
                res.OriginalContractStartDate = contractInfo.OriginalContractStartDate;

                res.PartyAAddress = contractInfo.PartyAAddress;
                res.PartyACode = contractInfo.PartyACode;
                res.PartyALegalPerson = contractInfo.PartyALegalPerson;
                res.PartyAName = contractInfo.PartyAName;
                res.PartyCAddress = string.Empty;
                res.PartyCLegalPerson = string.Empty;
                res.PartyCName = string.Empty;
                res.PartyCCode = string.Empty;
                res.PartyACode = contractInfo.PartyCCode;
                res.PartyAName = contractInfo.PartyCName;
                res.PartyAAddress = contractInfo.PartyCAddress;
                res.PartyALegalPerson = contractInfo.PartyCLegalPerson;
            }
            res.ChangeContractCode = $"{workNumber}-BG{ind.ToString("000000")}";
            res.Mobile = _dbPlatform.Queryable<UserExtension>().Where(t => t.Sapemployeeid == workNumber).First()?.Mobile ?? "";
            return res;
        }

        /// <summary>
        /// 保存变更合同
        /// </summary>
        /// <param name="entity">数据</param>
        /// <param name="type">1：立即生效 2：电子签</param>
        public async Task<string> SaveUserChangeContract(UserContractTripartiteAgreementEntity entity, int type)
        {
            string msg = "失败";
            if (entity != null)
            {
                var user = _dbPlatform.Queryable<Users>().Where(p => p.WorkNumber == CurrentUser.UserName).Select(p => p).First();
                entity.Id = Guid.NewGuid().ToString();
                entity.CreateDate = DateTime.Now;
                entity.CreateUser = user.UserName;
                entity.CreateWorkNumber = CurrentUser.UserName;
                entity.ChangeStatus = ContractChangeStatusEnum.已作废.GetHashCode();
                entity.ChangeStatusName = "异常作废";
                entity.SFPushStatus = 0;
                entity.SFPushDate = null;
                entity.SFPushErrorMsg = string.Empty;
                _dbPlatform.Insertable(entity).ExecuteCommand();

                // 立即生效
                if (type == 1)
                {
                    _contractStatusService.UpdateChangeContractStatus(entity.Id, ContractEnum.ContractChangeStatusEnum.签署成功);
                    msg = "成功";
                }

                // 电子签
                if (type == 2)
                {
                    var values = PackageValues(entity);
                    var memory = MiniWordHelper.CreatePdf(WordTemplateEnum.ContractChangeTemplate, values);

                    // 上传PDF签署文件
                    entity.FileKey = await _eSService.UploadSignFile(entity.ChangeContractCode, memory);
                    _dbPlatform.Updateable(entity).UpdateColumns(t => t.FileKey).ExecuteCommand();

                    // 合同三方变更签署流程发起
                    var taskId = await _eSService.CreateSignFlow(entity.ChangeContractCode, ESBusinessEnum.ContractChange);
                    if (!string.IsNullOrEmpty(taskId))
                    {
                        _contractStatusService.UpdateChangeContractStatus(entity.Id, ContractEnum.ContractChangeStatusEnum.签署中);
                        msg = "成功";
                    }
                }
            }

            return msg;
        }

        /// <summary>
        /// 根据条件查询--合同主体变更三方协议记录查询
        /// </summary>
        /// <param name="searchQuery"></param>
        /// <returns></returns>
        public async Task<PagedResultDto<ContractUserListDto>> GetTripartiteHistoryList(SearchQueryDto searchQuery)
        {
            RefAsync<int> totalCount = 0;
            var currentWorkNumber = CurrentUser != null && CurrentUser.UserName != null ? CurrentUser.UserName : string.Empty;
            var userCodes = _commonService.GetUserByRoleFilter(currentWorkNumber, RelationshipTypeEnum.ER);
            userCodes = userCodes != null && userCodes.Count > 0 ? userCodes : new List<string>() { "-1" };
            bool isAdmin = userCodes.Contains("9999999999");
            var result = await _dbPlatform.Queryable<UserContractTripartiteAgreementEntity, UserContractEntity>(
                (p, a) => new object[]
            {
                JoinType.Left, p.OriginalContractCode == a.ContractCode
            })
                 .WhereIF(!string.IsNullOrEmpty(searchQuery.WorkNumber), (p, a) => p.PartyBCode.Contains(searchQuery.WorkNumber))
                 .WhereIF(!string.IsNullOrEmpty(searchQuery.Name), (p, a) => p.PartyBName.Contains(searchQuery.Name))
                 .WhereIF(!string.IsNullOrEmpty(searchQuery.ContractCode), (p, a) => p.OriginalContractCode.Contains(searchQuery.ContractCode))
                 .WhereIF(!string.IsNullOrEmpty(searchQuery.ChangeContractCode), (p, a) => p.ChangeContractCode.Contains(searchQuery.ChangeContractCode))
                 .WhereIF(!string.IsNullOrEmpty(searchQuery.PartyA), (p, a) => p.PartyAName.Contains(searchQuery.PartyA) || p.PartyACode.Contains(searchQuery.PartyC))
                 .WhereIF(!string.IsNullOrEmpty(searchQuery.PartyC), (p, a) => p.PartyCName.Contains(searchQuery.PartyC) || p.PartyCCode.Contains(searchQuery.PartyC))
                 .WhereIF(!string.IsNullOrEmpty(searchQuery.Region), (p, a) => a.UserRegionCode.Contains(searchQuery.Region) || a.UserRegionName.Contains(searchQuery.Region))
                 .WhereIF(!string.IsNullOrEmpty(searchQuery.Department), (p, a) => a.DepartName.Contains(searchQuery.Department))
                 .WhereIF(searchQuery.ContractStartDate != null, (p, a) => p.ContractChangeDate >= searchQuery.ContractStartDate)
                 .WhereIF(searchQuery.ContractEndDate != null, (p, a) => p.ContractChangeDate <= searchQuery.ContractEndDate)
                 .WhereIF(searchQuery.ChangeStatus != null && searchQuery.ChangeStatus != -1, (p, a) => p.ChangeStatus == searchQuery.ChangeStatus)
                 .WhereIF(!isAdmin, (p, a) => userCodes.Contains(p.PartyBCode))
                 .OrderBy((p, a) => p.ContractChangeDate, OrderByType.Desc)
                 .Select((p, a) => new ContractUserListDto()
                 {
                     WorkNumber = p.PartyBCode,
                     Name = p.PartyBName,
                     ContractCode = p.OriginalContractCode,
                     ChangeContractCode = p.ChangeContractCode,
                     PartyAName = p.PartyAName,
                     PartyCName = p.PartyCName,
                     ChangeStatus = p.ChangeStatus,
                     ChangeStatusName = p.ChangeStatusName,
                     ContractChangeDate = p.ContractChangeDate,
                     RegionName = a.UserRegionName,
                     DepartmentName = a.DepartName,
                     ProcessId = p.SignFlowId,
                     SFPushStatus = p.SFPushStatus,
                     SFPushMessage = p.SFPushErrorMsg
                 })
                 .ToPageListAsync(searchQuery.PageIndex, searchQuery.PageSize, totalCount);

            if (result != null && result.Count() > 0)
            {
                foreach (var item in result)
                {
                    item.SFPushMessage = item.SFPushStatus == 1 ? "推送成功" : item.SFPushStatus == -1 ? ("推送失败:" + (item.SFPushMessage ?? "")) : "未推送";
                    item.IsCanCancel = (item.ChangeStatus == (int)ContractEnum.ContractChangeStatusEnum.签署中 || item.ChangeStatus == (int)ContractEnum.ContractChangeStatusEnum.签署成功);
                }
            }

            return new PagedResultDto<ContractUserListDto>()
            {
                TotalCount = totalCount,
                Items = result
            };
        }
        #endregion

        /// <summary>
        /// 导出数据报表
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<MemoryStream> ExportResultAsync(SearchQueryDto search)
        {
            search.PageSize = int.MaxValue;
            List<ContractUserListDto> result = new List<ContractUserListDto>();
            int sheetIndex = 0;
            if (search.ExcelType == 1)
            {
                sheetIndex = 0;
                var res = await GetContractUserFormationList(search);
                result = res?.Items?.ToList();
            }
            if (search.ExcelType == 2)
            {
                sheetIndex = 1;
                var res = await GetContractUserRenewalList(search);
                result = res?.Items?.ToList();
            }
            if (search.ExcelType == 3)
            {
                sheetIndex = 2;
                var res = await GetContractUserRenewalList(search);
                result = res?.Items?.ToList();
            }
            if (search.ExcelType == 4)
            {
                sheetIndex = 3;
                var res = await GetContractUserRenewalList(search);
                result = res?.Items?.ToList();
            }
            if (search.ExcelType == 5)
            {
                sheetIndex = 4;
                var res = await GetAllTripartiteContract(search);
                result = res?.Items?.ToList();
            }

            if (search.ExcelType == 6)
            {
                sheetIndex = 5;
                var res = await GetTripartiteHistoryList(search);
                result = res?.Items?.ToList();
            }

            var fs = File.OpenRead(Environment.CurrentDirectory + "/wwwroot/excel/合同列表导出模板.xlsx");
            IWorkbook workbook = new XSSFWorkbook(fs);
            ISheet sheet = workbook.GetSheetAt(sheetIndex);
            for (var i = 5; i >= 0; i--)
            {
                if (i != sheetIndex)
                {
                    workbook.RemoveSheetAt(i);
                }
            }
            #region // 边框样式

            // 全局样式
            ICellStyle borderStyle = workbook.CreateCellStyle();
            borderStyle.BorderLeft = BorderStyle.Thin;
            borderStyle.BorderRight = BorderStyle.Thin;
            borderStyle.BorderTop = BorderStyle.Thin;
            borderStyle.BorderBottom = BorderStyle.Thin;
            #endregion

            for (int i = 0; i < result.Count; i++)
            {
                List<string> columns = new List<string>();
                // 合同订立
                if (search.ExcelType == 1)
                {
                    columns.Add(result[i].WorkNumber);
                    columns.Add(result[i].Name);
                    columns.Add(result[i].RegionName);
                    columns.Add(result[i].DepartmentName);
                    columns.Add(result[i].Position);
                    columns.Add(result[i].JoinedDate);
                }
                // 合同续订
                if (search.ExcelType == 2)
                {
                    columns.Add(result[i].WorkNumber);
                    columns.Add(result[i].Name);
                    columns.Add(result[i].RegionName);
                    columns.Add(result[i].DepartmentName);
                    columns.Add(result[i].ContractCode);
                    columns.Add(result[i].ContractStartDate?.ToString("yyyy-MM-dd"));
                    columns.Add(result[i].ContractEndDate?.ToString("yyyy-MM-dd"));
                    columns.Add(result[i].ContractTypeName);
                    columns.Add(result[i].ContractDeadLineName);
                    columns.Add(result[i].ContractCount.ToString());
                    columns.Add(result[i].Position);
                    columns.Add(result[i].ContractRenewalStatusName);
                }
                // 合同续订流程
                if (search.ExcelType == 3)
                {
                    columns.Add(result[i].ProcessId);
                    columns.Add(result[i].WorkNumber);
                    columns.Add(result[i].Name);
                    columns.Add(result[i].RegionName);
                    columns.Add(result[i].DepartmentName);
                    columns.Add(result[i].ContractCode);
                    columns.Add(result[i].ContractStartDate?.ToString("yyyy-MM-dd"));
                    columns.Add(result[i].ContractEndDate?.ToString("yyyy-MM-dd"));
                    columns.Add(result[i].ContractTypeName);
                    columns.Add(result[i].ContractDeadLineName);
                    columns.Add(result[i].ContractCount.ToString());
                    columns.Add(result[i].Position);
                    columns.Add(result[i].ContractRenewalStatusName);
                }
                // 合同列表
                if (search.ExcelType == 4)
                {
                    columns.Add(result[i].WorkNumber);
                    columns.Add(result[i].Name);
                    columns.Add(result[i].RegionName);
                    columns.Add(result[i].DepartmentName);
                    columns.Add(result[i].ContractCode);
                    columns.Add(result[i].ContractStartDate?.ToString("yyyy-MM-dd"));
                    columns.Add(result[i].ContractEndDate?.ToString("yyyy-MM-dd"));
                    columns.Add(result[i].ContractTypeName);
                    columns.Add(result[i].ContractDeadLineName);
                    columns.Add(result[i].ContractCount.ToString());
                    columns.Add(result[i].Position);
                    columns.Add(result[i].IsSign);
                    columns.Add(result[i].SignFlowId);
                    columns.Add(result[i].ContractStatusName);
                }
                // 合同变更
                if (search.ExcelType == 5)
                {
                    columns.Add(result[i].WorkNumber);
                    columns.Add(result[i].Name);
                    columns.Add(result[i].ContractCode);
                    columns.Add(result[i].RegionName);
                    columns.Add(result[i].DepartmentName);
                    columns.Add(result[i].ContractStartDate?.ToString("yyyy-MM-dd"));
                    columns.Add(result[i].ContractEndDate?.ToString("yyyy-MM-dd"));
                    columns.Add(result[i].ContractTypeName);
                    columns.Add(result[i].ContractDeadLineName);
                    columns.Add(result[i].ChangeStatusName);
                }
                // 合同变更历史记录
                if (search.ExcelType == 6)
                {
                    columns.Add(result[i].WorkNumber);
                    columns.Add(result[i].Name);
                    columns.Add(result[i].ChangeContractCode);
                    columns.Add(result[i].ContractCode);
                    columns.Add(result[i].RegionName);
                    columns.Add(result[i].DepartmentName);
                    columns.Add(result[i].ContractChangeDate?.ToString("yyyy-MM-dd"));
                    columns.Add(result[i].PartyAName);
                    columns.Add(result[i].PartyCName);
                    columns.Add(result[i].ChangeStatusName);
                }
                IRow row = sheet.CreateRow(i + 1);
                for (int j = 0; j < columns.Count; j++)
                {
                    ICell cell = row.CreateCell(j);
                    cell.SetCellValue(columns[j]);
                    cell.CellStyle = borderStyle;
                }
            }

            MemoryStream memoryStream = new MemoryStream();
            workbook.Write(memoryStream);
            workbook.Close();
            return memoryStream;
        }

        /// <summary>
        /// 根据查询条件查询-合同续签审批报表
        /// </summary>
        /// <param name="searchQuery">查询条件</param>
        /// <returns></returns>
        public async Task<PagedResultDto<UserContractRenewalProcessEntity>> GetContractRenewalProcessByFilter(SearchQueryDto searchQuery)
        {
            RefAsync<int> totalCount = 0;
            var currentWorkNumber = CurrentUser != null && CurrentUser.UserName != null ? CurrentUser.UserName : string.Empty;
            var userCodes = _commonService.GetUserByRoleFilter(currentWorkNumber, RelationshipTypeEnum.HRBP);
            userCodes = userCodes != null && userCodes.Count > 0 ? userCodes : new List<string>() { "-1" };
            bool isAdmin = userCodes.Contains("9999999999");
            var result = await _dbBussiness.Queryable<UserContractRenewalProcessEntity>()
                 .WhereIF(!string.IsNullOrEmpty(searchQuery.Name), p => p.UserName.Contains(searchQuery.Name))
                 .WhereIF(!string.IsNullOrEmpty(searchQuery.ProcessId), p => p.Id.Contains(searchQuery.ProcessId))
                 .WhereIF(!string.IsNullOrEmpty(searchQuery.WorkNumber), p => p.WorkNumber.Contains(searchQuery.WorkNumber))
                 .WhereIF(searchQuery.ContractStatusCode != null && searchQuery.ContractStatusCode != -1, p => p.Status == searchQuery.ContractStatusCode.ToString())
                 .WhereIF(searchQuery.ContractStartDate != null, p => p.ApproveDate >= searchQuery.ContractStartDate)
                 .WhereIF(searchQuery.ContractEndDate != null, p => p.ApproveDate <= searchQuery.ContractEndDate)
                 .WhereIF(searchQuery.LowerScope != null, p => decimal.Parse(p.TotalScope) >= searchQuery.LowerScope)
                 .WhereIF(searchQuery.HighestScope != null, p => decimal.Parse(p.TotalScope) <= searchQuery.HighestScope)
                 .WhereIF(!isAdmin, p => userCodes.Contains(p.WorkNumber))
                 .Select(p => p)
                 .ToPageListAsync(searchQuery.PageIndex, searchQuery.PageSize, totalCount);

            return new PagedResultDto<UserContractRenewalProcessEntity>()
            {
                TotalCount = totalCount,
                Items = result
            };
        }

        /// <summary>
        /// 获取新员工
        /// </summary>
        /// <param name="workNumber"></param>
        /// <returns></returns>
        public object GetNewStaff(string workNumber)
        {
            // 更新HANA数据
            return new List<object>(){
                new {Name = "姓名", WorkNumber = "111", Region = "苏州", Department = "12333", Position = "岗位" } };
        }

        #region 私有方法
        private Dictionary<string, object> PackageValuesByType(bool isRenewal, bool isIU, UserContractEntity entity)
        {
            var icon = Environment.CurrentDirectory + "/wwwroot/docs/wordTemplate/icon1.png"; // 打勾图标
            var icon1 = Environment.CurrentDirectory + "/wwwroot/docs/wordTemplate/icon2.png"; // 空白图标
            // 有固定期限合同
            bool isRegularContract = entity.ContractDeadLineCode == ContractEnum.ContractTypeEnum.中长期合同.GetHashCode() ||
                entity.ContractDeadLineCode == ContractEnum.ContractTypeEnum.短期合同.GetHashCode();
            // 无固定期限合同
            bool isNoFixedContract = entity.ContractDeadLineCode == ContractEnum.ContractTypeEnum.无固定期限.GetHashCode();
            // 以完成一定工作任务为期限的合同
            bool isOneYearContract = !isRegularContract && !isNoFixedContract;
            // 是否是农业户口
            bool isFarming = !string.IsNullOrEmpty(entity.CategoryType) && entity.CategoryType.StartsWith("农业");
            Dictionary<string, object> values = new Dictionary<string, object>
            {
                { "WNum", entity.WorkNumber },
                { "CorporateName", entity.CorporateName },
                { "UserName", entity.UserName },
                { "CorporateAddress", entity.CorporateAddress },
                { "CorporatePostCode", entity.CorporatePostCode },
                { "CorporatePhone", entity.CorporatePhone },
                { "CardNum", entity.CardNum },
                { "CardAddress", entity.CardAddress },
                { "NonfarmImg", isFarming ? new MiniWordPicture() { Path = icon1, Width = 20, Height = 20 } : new MiniWordPicture() { Path = icon, Width = 20, Height = 20 } },
                { "FarmingImg", isFarming ? new MiniWordPicture() { Path = icon, Width = 20, Height = 20 } : new MiniWordPicture() { Path = icon1, Width = 20, Height = 20 } },
                { "ContactAddress", entity.ContactAddress },
                { "WorkAddressName", entity.WorkAddressName },
                { "PostCode", entity.PostCode },
                { "MobilePhone", entity.MobilePhone },
                { "RegularImg", isRegularContract ? new MiniWordPicture() { Path = icon, Width = 20, Height = 20 } : new MiniWordPicture() { Path = icon1, Width = 20, Height = 20 } },
                { "StartY", isRegularContract ? entity.ContractStartDate?.Year.ToString() : " / " },
                { "StartM", isRegularContract ? entity.ContractStartDate?.Month.ToString() : " / " },
                { "StartD", isRegularContract ? entity.ContractStartDate?.Day.ToString() : " / " },
                { "EndY", isRegularContract ? entity.ContractEndDate?.Year.ToString() : " / " },
                { "EndM", isRegularContract ? entity.ContractEndDate?.Month.ToString() : " / " },
                { "EndD", isRegularContract ? entity.ContractEndDate?.Day.ToString() : " / " },
                { "SYQStartY", isRegularContract && entity.HasSYQ ? entity.SYQStartDate?.Year.ToString() : " / " },
                { "SYQStartM", isRegularContract && entity.HasSYQ ? entity.SYQStartDate?.Month.ToString() : " / " },
                { "SYQStartD", isRegularContract && entity.HasSYQ ? entity.SYQStartDate?.Day.ToString() : " / " },
                { "SYQEndY", isRegularContract && entity.HasSYQ ? entity.SYQEndDate?.Year.ToString() : " / " },
                { "SYQEndM", isRegularContract && entity.HasSYQ ? entity.SYQEndDate?.Month.ToString() : " / " },
                { "SYQEndD", isRegularContract && entity.HasSYQ ? entity.SYQEndDate?.Day.ToString() : " / " },
                { "NoFixedImg", isNoFixedContract ? new MiniWordPicture() { Path = icon, Width = 20, Height = 20 } : new MiniWordPicture() { Path = icon1, Width = 20, Height = 20 } },
                { "NoFixedStartY", isNoFixedContract ? entity.ContractStartDate?.Year.ToString() : " / " },
                { "NoFixedStartM", isNoFixedContract ? entity.ContractStartDate?.Month.ToString() : " / " },
                { "NoFixedStartD", isNoFixedContract ? entity.ContractStartDate?.Day.ToString() : " / " },
                { "TimeSheetType", entity.TimeSheetType },
                { "OtherImg", isOneYearContract ? new MiniWordPicture() { Path = icon, Width = 20, Height = 20 } : new MiniWordPicture() { Path = icon1, Width = 20, Height = 20 } },
                { "SYQMonth", entity.SYQMonth },
                { "Position", entity.UserPosnc },
                { "ContractSYQY", entity.SYQStartDate?.Year },
                { "ContractSYQM", entity.SYQStartDate?.Month },
                { "ContractSYQD", entity.SYQStartDate?.Day },
                { "ContractSYQEndY", entity.SYQEndDate?.Year },
                { "ContractSYQEndM", entity.SYQEndDate?.Month },
                { "ContractSYQEndD", entity.SYQEndDate?.Day },
                { "ContractStartY", entity.ContractStartDate?.Year },
                { "ContractStartM", entity.ContractStartDate?.Month },
                { "ContractStartD", entity.ContractStartDate?.Day },
                { "NoFixedSYQStartY", isNoFixedContract ? entity.SYQEndDate?.Year.ToString() : " / " },
                { "NoFixedSYQStartM", isNoFixedContract ? entity.SYQEndDate?.Month.ToString() : " / " },
                { "NoFixedSYQStartD", isNoFixedContract ? entity.SYQEndDate?.Day.ToString() : " / " },
                { "NoFixedSYQEndY", " / " },
                { "NoFixedSYQEndM", " / " },
                { "NoFixedSYQEndD", " / " },
                { "CardName", entity.CardName },
                { "DepartName", entity.DepartName }
            };

            // 实习协议
            var corporate = _dbPlatform.Queryable<CorporateEntity>().Where(t => t.CorporateCode == entity.CorporateCode).First();
            values.Add("LegalPerson", corporate?.LegalPerson ?? "");
            values.Add("CreditCode", corporate?.CreditCode ?? "");
            if (entity.ContractCode.Contains("SX"))
            {
                values.Add("School", entity?.School);
                values.Add("Professional", entity?.Professional);
                values.Add("EndDate", entity?.EndDate?.ToString("yyyy-MM-dd"));
            }
            return values;
        }

        /// <summary>
        /// 拼接变更合同
        /// </summary>
        /// <param name="agreementEntity"></param>
        /// <returns></returns>
        private Dictionary<string, object> PackageValues(UserContractTripartiteAgreementEntity agreementEntity)
        {
            var originalContract = _dbPlatform.Queryable<UserContractEntity>().Where(p => p.ContractCode == agreementEntity.OriginalContractCode).Select(p => p).First();
            var icon = Environment.CurrentDirectory + "/wwwroot/docs/wordTemplate/icon1.png"; // 打勾图标
            var icon1 = Environment.CurrentDirectory + "/wwwroot/docs/wordTemplate/icon2.png"; // 空白图标
            var icon1Img = originalContract.ContractDeadLineCode == 3 ? icon1 : icon;
            var icon2Img = originalContract.ContractDeadLineCode == 3 ? icon : icon1;
            Dictionary<string, object> values = new Dictionary<string, object>() {
                { "PartyAName", agreementEntity.PartyAName },
                { "PartyAAddress", agreementEntity.PartyAAddress },
                { "PartyALegalPerson", agreementEntity.PartyALegalPerson },
                { "PartyBName", agreementEntity.PartyBName },
                { "PartyBAddress", agreementEntity.PartyBAddress },
                { "PartyBCardNo", agreementEntity.PartyBCardNo },
                { "PartyCName", agreementEntity.PartyCName },
                { "PartyCAddress", agreementEntity.PartyCAddress },
                { "PartyCLegalPerson", agreementEntity.PartyCLegalPerson },
                { "StartY", originalContract.SignDate?.Year },
                { "StartM", originalContract.SignDate?.Month },
                { "StartD", originalContract.SignDate?.Day},
                { "ChangeY", agreementEntity.ContractChangeDate?.Year },
                { "ChangeM", agreementEntity.ContractChangeDate?.Month },
                { "ChangeD", agreementEntity.ContractChangeDate?.Day},
                { "ContractStartY", originalContract.ContractStartDate?.Year },
                { "ContractStartM", originalContract.ContractStartDate?.Month },
                { "ContractStartD", originalContract.ContractStartDate?.Day },
                { "ContractEndY", originalContract.ContractEndDate?.Year },
                { "ContractEndM", originalContract.ContractEndDate?.Month },
                { "ContractEndD",originalContract.ContractEndDate?.Day },
                { "icon1Img",new MiniWordPicture() { Path = icon1Img, Width = 20, Height = 20 } },
                { "icon2Img",new MiniWordPicture() { Path = icon2Img, Width = 20, Height = 20 } },
            };
            return values;
        }

        private string ValidateEnetity(UserContractEntity entity)
        {
            string msg = string.Empty;
            if (string.IsNullOrEmpty(entity.DepartName))
            {
                msg += "部门不能为空<br/>";
            }
            if (string.IsNullOrEmpty(entity.UserPosnc))
            {
                msg += "职位不能为空<br/>";
            }
            if (string.IsNullOrEmpty(entity.WorkNumber))
            {
                msg += "工号不能为空<br/>";
            }
            if (string.IsNullOrEmpty(entity.UserName))
            {
                msg += "姓名不能为空<br/>";
            }
            if (string.IsNullOrEmpty(entity.MobilePhone))
            {
                msg += "员工电话不能为空<br/>";
            }
            if (string.IsNullOrEmpty(entity.CardName))
            {
                msg += "证件类型电话不能为空<br/>";
            }
            if (string.IsNullOrEmpty(entity.CardNum))
            {
                msg += "证件号码不能为空<br/>";
            }
            if (string.IsNullOrEmpty(entity.CategoryType))
            {
                msg += "户口性质不能为空<br/>";
            }
            if (string.IsNullOrEmpty(entity.CardAddress))
            {
                msg += "户籍地址不能为空<br/>";
            }
            if (string.IsNullOrEmpty(entity.ContactAddress))
            {
                msg += "联系地址不能为空<br/>";
            }
            if (string.IsNullOrEmpty(entity.PostCode))
            {
                msg += "员工邮编不能为空<br/>";
            }
            if (string.IsNullOrEmpty(entity.TimeSheetType))
            {
                msg += "工时属性不能为空<br/>";
            }
            if (string.IsNullOrEmpty(entity.ContractCode))
            {
                msg += "合同编码不能为空<br/>";
            }
            if (string.IsNullOrEmpty(entity.ContractTypeCode))
            {
                msg += "合同类型不能为空<br/>";
            }
            if (entity.ContractDeadLineCode == null)
            {
                msg += "合同期限类型不能为空<br/>";
            }
            if (entity.ContractStartDate == null)
            {
                msg += "合同开始时间不能为空<br/>";
            }
            if (entity.ContractEndDate == null)
            {
                msg += "合同结束时间不能为空<br/>";
            }
            if (entity.ContractStartDate == null)
            {
                msg += "合同开始时间不能为空<br/>";
            }
            if (string.IsNullOrEmpty(entity.WorkAddress))
            {
                msg += "工作地点不能为空<br/>";
            }
            if (entity.HasSYQ && entity.SYQStartDate == null)
            {
                msg += "试用期开始日期不能为空<br/>";
            }
            if (entity.HasSYQ && entity.SYQMonth == null)
            {
                msg += "试用期期限（月）不能为空<br/>";
            }
            if (entity.HasSYQ && entity.SYQEndDate == null)
            {
                msg += "试用期结束日期不能为空<br/>";
            }
            if (string.IsNullOrEmpty(entity.CorporateName))
            {
                msg += "甲方名称不能为空<br/>";
            }
            if (string.IsNullOrEmpty(entity.CorporatePostCode))
            {
                msg += "甲方邮编不能为空<br/>";
            }
            if (string.IsNullOrEmpty(entity.CorporateCode))
            {
                msg += "甲方公司代码不能为空<br/>";
            }
            if (string.IsNullOrEmpty(entity.CorporatePhone))
            {
                msg += "甲方电话不能为空<br/>";
            }
            if (string.IsNullOrEmpty(entity.CorporateAddress))
            {
                msg += "甲方地址不能为空<br/>";
            }
            return msg;
        }
        #endregion

        /// <summary>
        /// 测试合同模板
        /// </summary>
        /// <param name="entity">数据</param>
        /// <param name="type">1：立即生效 2：电子签</param>
        public async Task<byte[]> TestContractTemplate(UserContractEntity entity)
        {
            string msg = "失败";
            if (entity != null)
            {
                var message = ValidateEnetity(entity);
                if (!string.IsNullOrEmpty(message))
                {
                    msg = message;
                }
                else
                {
                    // 是否是续签
                    var currentWorkNumber = CurrentUser != null && CurrentUser.UserName != null ? CurrentUser.UserName : string.Empty;
                    bool isRenewal = !string.IsNullOrEmpty(entity.ParentContractCode);
                    var user = _dbPlatform.Queryable<Users>().Where(p => p.WorkNumber == currentWorkNumber).Select(p => p).First();
                    entity.ContractId = Guid.NewGuid().ToString();
                    entity.CreateDate = DateTime.Now;
                    entity.CreateUserName = user?.UserName;
                    entity.CreateUserWorkNumber = user?.WorkNumber;
                    entity.ContractStatus = ContractStatusEnum.已作废.GetHashCode();
                    entity.ContractRenewalStatus = null;
                    entity.ContractRenewalStatusName = string.Empty;
                    entity.SFPushStatus = 0;
                    entity.SFPushDate = null;
                    entity.SFPushErrorMsg = string.Empty;
                    entity.ContractStatusName = "异常作废";

                    ESBusinessEnum businessEnum = ESBusinessEnum.Contract;
                    // 合同模版名
                    WordTemplateEnum templateType = WordTemplateEnum.ContractTemplate;
                    Dictionary<string, object> values = null;

                    // 实习协议
                    if (entity.ContractCode.Contains("-SX-"))
                    {
                        businessEnum = ESBusinessEnum.InternContract;
                        templateType = WordTemplateEnum.InternContractTemplate;
                        values = PackageValuesByType(false, false, entity);
                    }
                    // 退休返聘
                    else if (entity.ContractCode.Contains("-FP-"))
                    {
                        businessEnum = ESBusinessEnum.RetirementContract;
                        templateType = entity.IsLB == 1 ? WordTemplateEnum.RetirementContractTemplate : WordTemplateEnum.RetirementOutContractTemplate;
                        values = PackageValuesByType(false, false, entity);
                    }
                    else
                    {
                        // 是否IU
                        var userareas = _dbPlatform.Queryable<Dictionaries>().Where(t => t.TypeCode == "HR_IU").Select(t => t.Value).ToList();
                        var userExtension = _dbPlatform.Queryable<UserExtension>()
                               .Where(a => a.Sapemployeeid == entity.WorkNumber && (userareas.Contains(a.Objid_1) || userareas.Contains(a.Objid_2) || userareas.Contains(a.Objid_3)))
                               .First();
                        // 是否IColor
                        var icoloruserareas = _dbPlatform.Queryable<Dictionaries>()
                             .Where(t => t.TypeCode == "HR_IColor").Select(t => t.Value).ToList();

                        var iColorUserExtension = _dbPlatform.Queryable<UserExtension>()
                              .Where(a => a.Sapemployeeid == entity.WorkNumber && (icoloruserareas.Contains(a.Objid_1) || icoloruserareas.Contains(a.Objid_2) || icoloruserareas.Contains(a.Objid_3)))
                              .First();

                        var isIU = userExtension == null ? false : true;
                        var isIColor = iColorUserExtension == null ? false : true;

                        // 合同模版名
                        templateType = isRenewal ?
                            (isIU ? WordTemplateEnum.IUContractRenewalTemplate : isIColor ? WordTemplateEnum.IColorContractRenewalTemplate : WordTemplateEnum.ContractRenewalTemplate) :
                            (isIU ? WordTemplateEnum.IUContractTemplate : isIColor ? WordTemplateEnum.IColorContractTemplate : WordTemplateEnum.ContractTemplate);
                        values = PackageValuesByType(isRenewal, isIU, entity);
                    }

                    var memory = MiniWordHelper.CreatePdf(templateType, values);

                    return memory;
                }
            }
            else
            {
                msg = "对不起，数据不能为空";
            }

            return new byte[] { };
        }

        /// <summary>
        /// 导入主体变更协议
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        public async Task<ImportResultExt<TripartiteAgreementContractImportDto>> ImportTripartiteAgreementContracts(List<TripartiteAgreementContractImportDto> data)
        {
            var rst = new ImportResultExt<TripartiteAgreementContractImportDto>
            {
                Data = data
            };

            var user = await _dbPlatform.Queryable<Users>().FirstAsync(x => x.WorkNumber == CurrentUser.UserName);
            if (user == null)
            {
                rst.Exception = new Exception($"用户不存在");
                return new ImportResultExt<TripartiteAgreementContractImportDto>()
                {
                    Exception = rst.Exception,
                    RowErrors = rst.RowErrors,
                    TemplateErrors = rst.TemplateErrors
                };
            }

            var corporates = _dbPlatform.Queryable<CorporateEntity>().ToList();

            var index = 2;
            var insertEntities = new List<UserContractTripartiteAgreementEntity>();
            foreach (var item in rst.Data)
            {
                var haveChangeStatusEnum = System.Enum.TryParse(item.ChangeStatusName, out ContractChangeStatusEnum changeStatusEnum);
                if (!haveChangeStatusEnum)
                {
                    rst.Exception = new Exception($"第{index}行，变更状态错误");
                    break;
                }
                if (!DateTime.TryParse(item.ContractChangeDate, out _))
                {
                    rst.Exception = new Exception($"第{index}行，缺少合同变更日期");
                    break;
                }
                var partA = corporates.FirstOrDefault(x => x.CorporateCode == item.PartyACode);
                if (partA == null)
                {
                    rst.Exception = new Exception($"第{index}行，甲方信息未找到");
                    break;
                }
                var partB = _dbPlatform.Queryable<UserExtensionSecondEntity>().First(x => x.WorkNumber == item.PartyBCode);
                if (partB == null)
                {
                    rst.Exception = new Exception($"第{index}行，乙方信息未找到");
                    break;
                }
                var partC = corporates.FirstOrDefault(x => x.CorporateCode == item.PartyCCode);
                if (partC == null)
                {
                    rst.Exception = new Exception($"第{index}行，丙方信息未找到");
                    break;
                }
                if (string.IsNullOrWhiteSpace(item.ChangeContractCode))
                {
                    rst.Exception = new Exception($"第{index}行，缺少变更合同协议号");
                    break;
                }
                if (string.IsNullOrWhiteSpace(item.OriginalContractCode))
                {
                    rst.Exception = new Exception($"第{index}行，缺少原合同编码");
                    break;
                }
                if (!DateTime.TryParse(item.OriginalContractStartDate, out _))
                {
                    rst.Exception = new Exception($"第{index}行，缺少原合同开始日期");
                    break;
                }
                if (!DateTime.TryParse(item.OriginalContractEndDate, out _))
                {
                    rst.Exception = new Exception($"第{index}行，缺少原合同结束日期");
                    break;
                }
                if (rst.Data.Count(x => x.OriginalContractCode == item.OriginalContractCode) > 1)
                {
                    rst.Exception = new Exception($"第{index}行，原合同编码在Excel中有重复");
                    break;
                }
                if (rst.Data.Count(x => x.ChangeContractCode == item.ChangeContractCode) > 1)
                {
                    rst.Exception = new Exception($"第{index}行，变更合同协议号在Excel中有重复");
                    break;
                }
                //if (_dbPlatform.Queryable<UserContractTripartiteAgreementEntity>().Any(x => x.OriginalContractCode == item.OriginalContractCode))
                //{
                //    rst.Exception = new Exception($"第{index}行，原合同编码已存在");
                //    break;
                //}
                if (_dbPlatform.Queryable<UserContractTripartiteAgreementEntity>().Any(x => x.ChangeContractCode == item.ChangeContractCode))
                {
                    rst.Exception = new Exception($"第{index}行，变更合同协议号已存在");
                    break;
                }
                var entity = new UserContractTripartiteAgreementEntity
                {
                    PartyACode = item.PartyACode,
                    PartyAName = partA.CorporateName,
                    PartyAAddress = partA.CorporateAddress,
                    PartyALegalPerson = partA.LegalPerson,
                    PartyBCode = item.PartyBCode,
                    PartyBName = partB.UserName,
                    PartyBAddress = partB.ContactAddress,
                    PartyBCardNo = partB.CardNum,
                    PartyCCode = item.PartyCCode,
                    PartyCName = partC.CorporateName,
                    PartyCAddress = partC.CorporateAddress,
                    PartyCLegalPerson = partC.LegalPerson,
                    OriginalContractCode = item.OriginalContractCode,
                    OriginalContractStartDate = item.OriginalContractStartDate.ToOurDateTime(),
                    OriginalContractEndDate = item.OriginalContractEndDate.ToOurDateTime(),
                    ChangeContractCode = item.ChangeContractCode,
                    ContractChangeDate = item.ContractChangeDate.ToOurDateTime(),
                    ChangeStatus = (int)changeStatusEnum,
                    ChangeStatusName = item.ChangeStatusName,
                    FileKey = item.FileKey,
                    SignFlowId = item.SignFlowId,
                    SignErrorMsg = item.SignErrorMsg,
                    Id = Guid.NewGuid().ToString(),
                    SFPushStatus = item.SFPushStatus == "未推送" ? 0 : 1,
                    CreateUser = user.UserName,
                    CreateDate = DateTime.Now,
                    CreateWorkNumber = CurrentUser.UserName,
                };
                insertEntities.Add(entity);

                index++;
            }

            if (!rst.HasError)
            {
                _dbPlatform.Insertable(insertEntities).ExecuteCommand();
            }

            var resp = new ImportResultExt<TripartiteAgreementContractImportDto>()
            {
                Exception = rst.Exception,
                RowErrors = rst.RowErrors,
                TemplateErrors = rst.TemplateErrors
            };
            return resp;
        }
    }
}
